USE db_OnlineJewelleryShopping
GO
--insert Employee
insert into Employees Values('DungVM','123456','vuManhdung','04/24/1990','1s','Hanoi','DungVM1@yahoo.com','1')
insert into Employees Values('DungVM','123456','vuManhdung','04/24/1990','1s','Hanoi','DungVM1@yahoo.com','2')
-- insert ShippingType
insert into ShippingType VALUES('VIA POST','2000','aa')
insert into ShippingType VALUES('COURIER','2000','aa')
--insert Card
insert into CardType values('VISA','12345')
insert into CardType values('MASTERCARD','12345')
--insert user
insert into Users VALUES('DungVM','123456','Vu Manh Dung','04/24/90','Male','0983152424','Ha Noi','DungVM@yahoo.com')
--Insert CateLogy
insert into Category VALUES('Diamond')
insert into Category VALUES('Jewellry')
insert into Category VALUES('Gold')
insert into Category VALUES('Stores')
--insert Diamond
insert into Products values('1','Diamond','3','../Images/Custormer/diamond1.jpg','3','4','5','6','Diamond')
insert into Products values('1','Diamond','3','../Images/Custormer/diamond2.jpg','3','4','5','6','Diamond')
insert into Products values('1','Diamond','3','../Images/Custormer/diamond3.jpg','3','4','5','6','Diamond')
--insert Jewelry
insert into Products values('2','Jewellry','3','../Images/Custormer/jewelry1.jpg','3','4','5','6','Jewelry')
insert into Products values('2','Jewellry','3','../Images/Custormer/jewelry2.jpg','3','4','5','6','Jewelry')
insert into Products values('2','Jewellry','3','../Images/Custormer/jewelry3.jpg','3','4','5','6','Jewelry')
--insert Gold
insert into Products values('3','Gold','3','../Images/Custormer/gold1.jpg','3','4','5','6','Gold')
insert into Products values('3','Gold','3','../Images/Custormer/gold2.jpg','3','4','5','6','Gold')
insert into Products values('3','Gold','3','../Images/Custormer/gold3.jpg','3','4','5','6','Gold')
-- insert stores
insert into Products values('4','Stores','3','../Images/Custormer/store1.jpg','3','4','5','6','Stores')
insert into Products values('4','Stores','3','../Images/Custormer/Store2.jpg','3','4','5','6','Stores')
insert into Products values('4','Stores','3','../Images/Custormer/store3.jpg','3','4','5','6','Stores')
GO

CREATE PROCEDURE ProductDetails
@ProductID int
as
SELECT ProductID,CatName,ProductName,Quantity,UrlImage,[Weight],Width,Lenght,Price,[Description]
FROM dbo.Products 
inner join dbo.Category
ON dbo.Products.CatID = dbo.Category.CatID 
WHERE ProductID =@ProductID

GO

--GioHang
CREATE  PROCEDURE ShopingView
@ProductID int
as
SELECT ProductID,ProductName,CatName,Quantity,[Weight],Width,Lenght,Price
FROM dbo.Products 
inner join dbo.Category
ON dbo.Products.CatID = dbo.Category.CatID 
where ProductID =@ProductID

GO

CREATE PROCEDURE LoadPay
as
SELECT CardTypeId,CardType FROM CardType

GO

CREATE PROCEDURE AddOrder
@UserID int,
@DateOfPurchase datetime,
@Status nvarchar(20)
as
INSERT INTO Orders(UserID,DateOfPurchase,[Status]) VALUES(@UserID,@DateOfPurchase,@Status)
GO

CREATE PROCEDURE GetOrderID
as
SELECT OrderId FROM Orders

GO 

CREATE PROCEDURE AddOderDetails
@OrderId int,
@ProductID int,
@Price float,
@Quantity int,
@Name nvarchar(50),
@Address nvarchar(100),
@Phone int,
@Comment nvarchar(MAX)
AS
INSERT INTO OrderDetails(OrderId,ProductID,Price,Quantity,Name,Address,Phone,Comment) VALUES(@OrderId,@ProductID,@Price,@Quantity,@Name,@Address,@Phone,@Comment)

GO

CREATE PROCEDURE Jewellry_Home
as
SELECT ProductID,UrlImage,Price  From Products
WHERE CatID = 2
ORDER BY ProductID DESC
GO

CREATE PROCEDURE Diamond_Home
as
SELECT ProductID,UrlImage ,Price From Products
WHERE CatID = 1
ORDER BY ProductID DESC

GO

CREATE  PROCEDURE Store_Home
as
SELECT ProductID,UrlImage,Price  From Products
WHERE CatID = 4
ORDER BY ProductID DESC

GO

CREATE PROCEDURE Gold_Home
as
SELECT ProductID,UrlImage,Price From Products
WHERE CatID = 3
ORDER BY ProductID DESC
GO
CREATE PROCEDURE ProductSimilar
@CatName nvarchar(30)
AS
SELECT Products.ProductID,Products.CatID,Products.UrlImage FROM Products
inner join Category
ON
Products.CatID = Category.CatID
WHERE CatName = @CatName
GO
CREATE PROCEDURE ViewAllOrder
as
SELECT * FROM Orders
WHERE Status = 'In Progress'
Order By DateOfPurchase
Desc
GO
CREATE PROCEDURE ViewUser
@OrderID int
as
SELECT FullName,DateOfPurchase,PhoneNumber FROM Users
inner join Orders
ON
Orders.UserID  = Users.UserID
WHERE OrderId = @OrderID
GO
CREATE PROCEDURE ViewAddress
@OrderId int
as
SELECT Address from OrderDetails
where OrderId = @OrderId

GO
CREATE PROCEDURE VIEWShippingType
as
SELECT TypeOfShippingId,ShippingTypeName FROm ShippingType
GO
CREATE PROCEDURE ShippingManager
@FullName nvarchar(50),
@PhoneNumber int,
@OrderId int,
@EmployeeID int,
@Address nvarchar(100),
@TypeOfShipping int,
@Description nvarchar(max)
as
INSERT INTO  ShippingDetails VALUES(@FullName,@PhoneNumber,@OrderId,@EmployeeID,@Address,@TypeOfShipping,@Description)
GO
CREATE PROCEDURE VendorChangeOrder
@OrderId int
AS
UPDATE Orders SET Status ='Completed'
WHERE OrderId = @OrderId

GO
CREATE PROCEDURE LoadCatelogy
as
SELECT CatID,CatName FROM Category

GO
CREATE  PROCEDURE AddnewProduct
@CatID int,
@ProductName nvarchar(50),
@Quantity int,
@UrlImage nvarchar(MAX),
@Weight float,
@Width float,
@Lenght float,
@Price float,
@Description nvarchar(50)
as
INSERT INTO Products VALUES(@CatID,@ProductName,@Quantity,'../Images/Custormer/'+@UrlImage,@Weight,@Width,@Lenght,@Price,@Description)

GO
CREATE PROCEDURE VendorViewUser
@UserID int
as
SELECT UserID,UserName, FullName, DateOfBirth, Gender, PhoneNumber,Address,Email FROM Users
WHERE UserID = @UserID
GO
CREATE PROCEDURE VendorSearchByUserID
@UserID int
as
SELECT * FROM Orders 
where UserID = @UserID
GO
CREATE PROCEDURE VendorSearchByOrderID
@OrderID int
as
SELECT * FROM Orders 
where OrderId = @OrderID
GO
select *from ShippingDetails
GO
CREATE PROCEDURE ProductInOrderDetail
@OrderId int
as
Select Products.ProductID,Products.ProductName,Products.Price,OrderDetails.Quantity
FROM Products
inner join OrderDetails
ON
Products.ProductID = OrderDetails.ProductID
WHERE OrderId = @OrderId
GO
CREATE PROCEDURE ViewConsigneeInformation
@OrderId int
as
SELECT OrderDetails.Name,OrderDetails.Address,OrderDetails.Phone FROM OrderDetails
WHERE OrderId = @OrderId
GO
CREATE PROCEDURE VendorViewAllProduct
as
SELECT Category.CatName,ProductID,ProductName,Quantity,Price FROM Products
Inner join Category
ON
Category.CatID = Products.CatID
Order By ProductID
DESC

GO
CREATE PROCEDURE VendorViewOrderDetail
@OrderID int
as
SELECT Users.FullName,Users.Address,Orders.DateOfPurchase FROM Orders
INNER JOIN Users
ON
Orders.UserID = Users.UserID
WHERE OrderId = @OrderID
GO
CREATE PROCEDURE VendorViewConsigneeInfomation
@OrderID int
as
SELECT OrderDetails.Name,OrderDetails.Address,OrderDetails.Phone FROM OrderDetails
inner join Orders
ON
OrderDetails.OrderId = Orders.OrderId
Where OrderDetails.OrderId = @OrderID
GO
CREATE PROCEDURE Register
@UserName nvarchar(50),
@PassWord nvarchar(50),
@FullName nvarchar(50),
@DateOfBirth datetime,
@Gender nvarchar(10),
@PhoneNumber int,
@Address nvarchar(50),
@Email nvarchar(50)
as
insert into Users Values(@UserName,@PassWord,@FullName,@DateOfBirth,@Gender,@PhoneNumber,@Address,@Email)
GO
CREATE PROCEDURE ChekRegis
as
SELECT UserName FROM Users
GO
CREATE PROCEDURE VendorSearchProductByName
@ProductName nvarchar(50)
as
SELECT Category.CatName,ProductID,ProductName,Quantity,Price FROM Products
Inner join Category
ON
Category.CatID = Products.CatID
Where ProductName like '%'+@ProductName+'%'
Order By ProductID
DESC
GO

CREATE PROCEDURE VendorSearchProductByID
@ProductID int
as
SELECT Category.CatName,ProductID,ProductName,Quantity,Price FROM Products
Inner join Category
ON
Category.CatID = Products.CatID
Where ProductID = @ProductID 
Order By ProductID
DESC
GO
CREATE PROCEDURE CusLogin
@UserName nvarchar(50),
@Password nvarchar(50)
as
SELECT * FROM Users 
WHERE UserName = @UserName
and
PassWord = @Password
GO
CREATE PROCEDURE GetIdByUserName
@UserName nvarchar(50)
as
SELECT UserID from Users
where UserName = @UserName
GO
CREATE PROCEDURE LoginManager
@UserName nvarchar(50),
@Password nvarchar(50)
as
SELECT EmployeeName,EmployeePassWord,Roles FROM Employees
WHERE EmployeeName = @UserName AND EmployeePassWord = @Password
AND Roles = 1
GO
CREATE PROCEDURE LoginManagerVendor
@UserName nvarchar(50),
@Password nvarchar(50)
as
SELECT EmployeeName,EmployeePassWord,Roles FROM Employees
WHERE EmployeeName = @UserName AND EmployeePassWord = @Password
AND Roles = 2

GO
CREATE PROCEDURE GetIdEmployee
@UserName nvarchar(50)
as
SELECT EmployeeID FROM Employees
Where EmployeeName = @UserName
GO
CREATE PROCEDURE ChangePass
@EmpID int,
@NewPass nvarchar(50)
as
UPDATE Employees SET EmployeePassWord = @NewPass
WHERE EmployeeID = @EmpID
GO
select * from Employees
GO
CREATE PROCEDURE ViewallProduct
as
SELECT UrlImage,ProductID,Price FROM Products
Order By ProductID
DESC
GO
CREATE  PROCEDURE ViewVendorInfomation
@UserName nvarchar(50)
as
SELECT FullName,DateOfBirth,Address,Email FROM Employees
WHERE EmployeeName = @UserName
